"""
数据库管理器

提供数据库连接、会话管理和基础CRUD操作
"""

import os
from typing import Dict, Any, List, Optional, Type
from datetime import datetime
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.exc import SQLAlchemyError
from contextlib import contextmanager
from pathlib import Path

from .models import Base, Strategy, Order, Trade, StrategyPerformance, BacktestResult, AccountBalance, SystemMetrics, Alert, DataFile
from ..core import unified_logger


class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self, config: Optional[Dict[str, Any]] = None):
        """
        初始化数据库管理器
        
        Args:
            config: 配置字典，包含数据库配置信息
        """
        self.logger = unified_logger.get_logger('database_manager')
        
        # 设置数据库URL
        if config is None or 'database' not in config:
            # 默认使用SQLite
            db_path = Path("./data/xcquant.db")
            db_path.parent.mkdir(parents=True, exist_ok=True)
            database_url = f"sqlite:///{db_path}"
        else:
            db_config = config['database']
            if db_config.get('type') == 'sqlite':
                db_path = db_config.get('path', './data/xcquant.db')
                if db_path == ':memory:':
                    database_url = "sqlite:///:memory:"
                else:
                    db_path = Path(db_path)
                    db_path.parent.mkdir(parents=True, exist_ok=True)
                    database_url = f"sqlite:///{db_path}"
            else:
                # 其他数据库类型的处理
                database_url = db_config.get('url', 'sqlite:///./data/xcquant.db')
        
        self.database_url = database_url
        self.engine = None
        self.SessionLocal = None
        self.session = None  # 添加session属性
        
        self._initialize_database()
    
    def initialize(self):
        """公共初始化方法（兼容性）"""
        return self._initialize_database()
    
    def _initialize_database(self):
        """初始化数据库连接"""
        try:
            # 创建数据库引擎
            if self.database_url.startswith('sqlite'):
                self.engine = create_engine(
                    self.database_url,
                    connect_args={"check_same_thread": False},
                    echo=False
                )
            else:
                self.engine = create_engine(
                    self.database_url,
                    pool_size=20,
                    max_overflow=30,
                    pool_pre_ping=True,
                    pool_recycle=3600,
                    echo=False
                )
            
            # 创建会话工厂
            self.SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=self.engine)
            
            # 创建默认会话
            self.session = self.SessionLocal()
            
            # 创建表
            Base.metadata.create_all(bind=self.engine)
            
            self.logger.info(f"数据库初始化成功: {self.database_url}")
            
        except Exception as e:
            self.logger.error(f"数据库初始化失败: {e}")
            raise
    
    def close(self):
        """关闭数据库连接"""
        if self.session:
            self.session.close()
        if self.engine:
            self.engine.dispose()
    
    @contextmanager
    def get_session(self):
        """获取数据库会话（上下文管理器）"""
        session = self.SessionLocal()
        try:
            yield session
            session.commit()
        except Exception as e:
            session.rollback()
            self.logger.error(f"数据库操作失败: {e}")
            raise
        finally:
            session.close()
    
    def create_strategy(self, strategy_data: Dict[str, Any]) -> str:
        """
        创建策略记录
        
        Args:
            strategy_data: 策略数据
            
        Returns:
            策略ID
        """
        with self.get_session() as session:
            strategy = Strategy(**strategy_data)
            session.add(strategy)
            session.flush()
            return strategy.id
    
    def update_strategy(self, strategy_id: str, update_data: Dict[str, Any]) -> bool:
        """
        更新策略记录
        
        Args:
            strategy_id: 策略ID
            update_data: 更新数据
            
        Returns:
            是否更新成功
        """
        with self.get_session() as session:
            strategy = session.query(Strategy).filter(Strategy.id == strategy_id).first()
            if strategy:
                for key, value in update_data.items():
                    setattr(strategy, key, value)
                strategy.updated_at = datetime.utcnow()
                return True
            return False
    
    def get_strategy(self, strategy_id: str) -> Optional[Dict[str, Any]]:
        """
        获取策略记录
        
        Args:
            strategy_id: 策略ID
            
        Returns:
            策略数据
        """
        with self.get_session() as session:
            strategy = session.query(Strategy).filter(Strategy.id == strategy_id).first()
            if strategy:
                return {
                    'id': strategy.id,
                    'name': strategy.name,
                    'type': strategy.type,
                    'symbol': strategy.symbol,
                    'exchange': strategy.exchange,
                    'market_type': strategy.market_type,
                    'config': strategy.config,
                    'status': strategy.status,
                    'created_at': strategy.created_at,
                    'updated_at': strategy.updated_at
                }
            return None
    
    def list_strategies(self, status: Optional[str] = None) -> List[Dict[str, Any]]:
        """
        获取策略列表
        
        Args:
            status: 策略状态过滤
            
        Returns:
            策略列表
        """
        with self.get_session() as session:
            query = session.query(Strategy)
            if status:
                query = query.filter(Strategy.status == status)
            
            strategies = query.all()
            return [{
                'id': s.id,
                'name': s.name,
                'type': s.type,
                'symbol': s.symbol,
                'exchange': s.exchange,
                'market_type': s.market_type,
                'config': s.config,
                'status': s.status,
                'created_at': s.created_at,
                'updated_at': s.updated_at
            } for s in strategies]
    
    def create_order(self, order_data: Dict[str, Any]) -> str:
        """
        创建订单记录
        
        Args:
            order_data: 订单数据
            
        Returns:
            订单ID
        """
        with self.get_session() as session:
            order = Order(**order_data)
            session.add(order)
            session.flush()
            return order.id
    
    def update_order(self, order_id: str, update_data: Dict[str, Any]) -> bool:
        """
        更新订单记录
        
        Args:
            order_id: 订单ID
            update_data: 更新数据
            
        Returns:
            是否更新成功
        """
        with self.get_session() as session:
            order = session.query(Order).filter(Order.id == order_id).first()
            if order:
                for key, value in update_data.items():
                    setattr(order, key, value)
                order.updated_at = datetime.utcnow()
                return True
            return False
    
    def get_orders_by_strategy(self, strategy_id: str, limit: int = 100) -> List[Dict[str, Any]]:
        """
        获取策略的订单列表
        
        Args:
            strategy_id: 策略ID
            limit: 限制数量
            
        Returns:
            订单列表
        """
        with self.get_session() as session:
            orders = session.query(Order).filter(
                Order.strategy_id == strategy_id
            ).order_by(Order.created_at.desc()).limit(limit).all()
            
            return [{
                'id': o.id,
                'strategy_id': o.strategy_id,
                'exchange_order_id': o.exchange_order_id,
                'symbol': o.symbol,
                'side': o.side,
                'type': o.type,
                'amount': o.amount,
                'price': o.price,
                'filled_amount': o.filled_amount,
                'filled_price': o.filled_price,
                'status': o.status,
                'fee': o.fee,
                'fee_currency': o.fee_currency,
                'grid_level': o.grid_level,
                'created_at': o.created_at,
                'updated_at': o.updated_at
            } for o in orders]
    
    def create_trade(self, trade_data: Dict[str, Any]) -> str:
        """
        创建交易记录
        
        Args:
            trade_data: 交易数据
            
        Returns:
            交易ID
        """
        with self.get_session() as session:
            trade = Trade(**trade_data)
            session.add(trade)
            session.flush()
            return trade.id
    
    def get_trades_by_strategy(self, strategy_id: str, limit: int = 100) -> List[Dict[str, Any]]:
        """
        获取策略的交易记录
        
        Args:
            strategy_id: 策略ID
            limit: 限制数量
            
        Returns:
            交易记录列表
        """
        with self.get_session() as session:
            trades = session.query(Trade).filter(
                Trade.strategy_id == strategy_id
            ).order_by(Trade.executed_at.desc()).limit(limit).all()
            
            return [{
                'id': t.id,
                'order_id': t.order_id,
                'strategy_id': t.strategy_id,
                'exchange_trade_id': t.exchange_trade_id,
                'symbol': t.symbol,
                'side': t.side,
                'amount': t.amount,
                'price': t.price,
                'fee': t.fee,
                'fee_currency': t.fee_currency,
                'profit_loss': t.profit_loss,
                'executed_at': t.executed_at
            } for t in trades]
    
    def save_strategy_performance(self, performance_data: Dict[str, Any]) -> str:
        """
        保存策略性能记录
        
        Args:
            performance_data: 性能数据
            
        Returns:
            性能记录ID
        """
        with self.get_session() as session:
            performance = StrategyPerformance(**performance_data)
            session.add(performance)
            session.flush()
            return performance.id
    
    def get_strategy_performance(self, strategy_id: str, days: int = 30) -> List[Dict[str, Any]]:
        """
        获取策略性能记录
        
        Args:
            strategy_id: 策略ID
            days: 获取天数
            
        Returns:
            性能记录列表
        """
        with self.get_session() as session:
            from datetime import timedelta
            start_date = datetime.utcnow() - timedelta(days=days)
            
            performances = session.query(StrategyPerformance).filter(
                StrategyPerformance.strategy_id == strategy_id,
                StrategyPerformance.date >= start_date
            ).order_by(StrategyPerformance.date.desc()).all()
            
            return [{
                'id': p.id,
                'strategy_id': p.strategy_id,
                'date': p.date,
                'total_return': p.total_return,
                'daily_return': p.daily_return,
                'cumulative_return': p.cumulative_return,
                'max_drawdown': p.max_drawdown,
                'volatility': p.volatility,
                'sharpe_ratio': p.sharpe_ratio,
                'total_trades': p.total_trades,
                'winning_trades': p.winning_trades,
                'losing_trades': p.losing_trades,
                'win_rate': p.win_rate,
                'initial_balance': p.initial_balance,
                'current_balance': p.current_balance,
                'total_profit': p.total_profit,
                'total_loss': p.total_loss,
                'created_at': p.created_at
            } for p in performances]
    
    def save_backtest_result(self, backtest_data: Dict[str, Any]) -> str:
        """
        保存回测结果
        
        Args:
            backtest_data: 回测数据
            
        Returns:
            回测结果ID
        """
        with self.get_session() as session:
            backtest = BacktestResult(**backtest_data)
            session.add(backtest)
            session.flush()
            return backtest.id
    
    def get_backtest_results(self, strategy_name: Optional[str] = None, limit: int = 50) -> List[Dict[str, Any]]:
        """
        获取回测结果列表
        
        Args:
            strategy_name: 策略名称过滤
            limit: 限制数量
            
        Returns:
            回测结果列表
        """
        with self.get_session() as session:
            query = session.query(BacktestResult)
            if strategy_name:
                query = query.filter(BacktestResult.strategy_name == strategy_name)
            
            results = query.order_by(BacktestResult.created_at.desc()).limit(limit).all()
            
            return [{
                'id': r.id,
                'strategy_name': r.strategy_name,
                'strategy_config': r.strategy_config,
                'symbol': r.symbol,
                'exchange': r.exchange,
                'start_date': r.start_date,
                'end_date': r.end_date,
                'initial_balance': r.initial_balance,
                'final_balance': r.final_balance,
                'total_return': r.total_return,
                'annual_return': r.annual_return,
                'max_drawdown': r.max_drawdown,
                'sharpe_ratio': r.sharpe_ratio,
                'volatility': r.volatility,
                'total_trades': r.total_trades,
                'winning_trades': r.winning_trades,
                'losing_trades': r.losing_trades,
                'win_rate': r.win_rate,
                'created_at': r.created_at
            } for r in results]
    
    def update_account_balance(self, balance_data: Dict[str, Any]) -> bool:
        """
        更新账户余额
        
        Args:
            balance_data: 余额数据
            
        Returns:
            是否更新成功
        """
        with self.get_session() as session:
            balance = session.query(AccountBalance).filter(
                AccountBalance.exchange == balance_data['exchange'],
                AccountBalance.currency == balance_data['currency']
            ).first()
            
            if balance:
                # 更新现有记录
                for key, value in balance_data.items():
                    setattr(balance, key, value)
                balance.updated_at = datetime.utcnow()
            else:
                # 创建新记录
                balance = AccountBalance(**balance_data)
                session.add(balance)
            
            return True
    
    def get_account_balances(self, exchange: Optional[str] = None) -> List[Dict[str, Any]]:
        """
        获取账户余额列表
        
        Args:
            exchange: 交易所过滤
            
        Returns:
            余额列表
        """
        with self.get_session() as session:
            query = session.query(AccountBalance)
            if exchange:
                query = query.filter(AccountBalance.exchange == exchange)
            
            balances = query.all()
            
            return [{
                'id': b.id,
                'exchange': b.exchange,
                'currency': b.currency,
                'free_balance': b.free_balance,
                'locked_balance': b.locked_balance,
                'total_balance': b.total_balance,
                'updated_at': b.updated_at
            } for b in balances]
    
    def create_alert(self, alert_data: Dict[str, Any]) -> str:
        """
        创建告警记录
        
        Args:
            alert_data: 告警数据
            
        Returns:
            告警ID
        """
        with self.get_session() as session:
            alert = Alert(**alert_data)
            session.add(alert)
            session.flush()
            return alert.id
    
    def get_active_alerts(self, limit: int = 100) -> List[Dict[str, Any]]:
        """
        获取活跃告警列表
        
        Args:
            limit: 限制数量
            
        Returns:
            告警列表
        """
        with self.get_session() as session:
            alerts = session.query(Alert).filter(
                Alert.status == 'active'
            ).order_by(Alert.created_at.desc()).limit(limit).all()
            
            return [{
                'id': a.id,
                'level': a.level,
                'title': a.title,
                'message': a.message,
                'source': a.source,
                'strategy_id': a.strategy_id,
                'status': a.status,
                'created_at': a.created_at
            } for a in alerts]
    
    def record_data_file(self, file_data: Dict[str, Any]) -> str:
        """
        记录数据文件信息
        
        Args:
            file_data: 文件数据
            
        Returns:
            文件记录ID
        """
        with self.get_session() as session:
            data_file = DataFile(**file_data)
            session.add(data_file)
            session.flush()
            return data_file.id
    
    def get_data_files(self, exchange: Optional[str] = None, symbol: Optional[str] = None) -> List[Dict[str, Any]]:
        """
        获取数据文件列表
        
        Args:
            exchange: 交易所过滤
            symbol: 交易对过滤
            
        Returns:
            文件列表
        """
        with self.get_session() as session:
            query = session.query(DataFile)
            if exchange:
                query = query.filter(DataFile.exchange == exchange)
            if symbol:
                query = query.filter(DataFile.symbol == symbol)
            
            files = query.order_by(DataFile.created_at.desc()).all()
            
            return [{
                'id': f.id,
                'exchange': f.exchange,
                'market_type': f.market_type,
                'symbol': f.symbol,
                'interval': f.interval,
                'file_path': f.file_path,
                'file_size': f.file_size,
                'record_count': f.record_count,
                'start_time': f.start_time,
                'end_time': f.end_time,
                'created_at': f.created_at,
                'updated_at': f.updated_at
            } for f in files]
    
    def cleanup_old_data(self, days: int = 90) -> int:
        """
        清理旧数据
        
        Args:
            days: 保留天数
            
        Returns:
            清理的记录数
        """
        with self.get_session() as session:
            from datetime import timedelta
            cutoff_date = datetime.utcnow() - timedelta(days=days)
            
            # 清理旧的系统指标
            deleted_metrics = session.query(SystemMetrics).filter(
                SystemMetrics.timestamp < cutoff_date
            ).delete()
            
            # 清理已解决的告警
            deleted_alerts = session.query(Alert).filter(
                Alert.status == 'resolved',
                Alert.created_at < cutoff_date
            ).delete()
            
            total_deleted = deleted_metrics + deleted_alerts
            self.logger.info(f"清理了 {total_deleted} 条旧数据")
            
            return total_deleted
    
    def get_database_stats(self) -> Dict[str, Any]:
        """
        获取数据库统计信息
        
        Returns:
            统计信息
        """
        with self.get_session() as session:
            stats = {}
            
            # 统计各表记录数
            stats['strategies'] = session.query(Strategy).count()
            stats['orders'] = session.query(Order).count()
            stats['trades'] = session.query(Trade).count()
            stats['backtest_results'] = session.query(BacktestResult).count()
            stats['alerts'] = session.query(Alert).count()
            stats['data_files'] = session.query(DataFile).count()
            
            # 活跃策略数
            stats['active_strategies'] = session.query(Strategy).filter(
                Strategy.status == 'running'
            ).count()
            
            # 今日交易数
            today = datetime.utcnow().date()
            stats['today_trades'] = session.query(Trade).filter(
                Trade.executed_at >= today
            ).count()
            
            return stats